﻿using System;
using System.Collections.Generic;
using System.Text;
using AspNetCs.IDAL;
using AspNetCs.Model;
using AspNetCs.Common;
using System.Data;
using AspNetCs.Config;
using System.Data.SqlClient;

namespace AspNetCs.SQLServerDAL
{
    public class User_DAL : IUser
    {
        #region IUser 成员
        public int updatePwd(Users_Model model) {

            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[2];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue = model.UserId;
            par[0].parName = "@UserId";

            par[1].dbType = SqlDbType.VarChar;
            par[1].objValue = model.Pwd;
            par[1].parName = "@Pwd";

            #endregion


            db Con = new db();
            try
            {
                result = Con.ExcSql("Update  [Users] Set Pwd=@Pwd where Userid=@UserId", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);

            }
            finally
            {
                Con.close();

            }


            return result;
        }

        public int update(Users_Model model)
        {
            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[10];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue = model.UserId;
            par[0].parName = "@UserId";

            par[1].dbType = SqlDbType.VarChar;
            par[1].objValue = model.UserEmail;
            par[1].parName = "@UserEmail";

            par[2].dbType = SqlDbType.VarChar;
            par[2].objValue = model.Sex;
            par[2].parName = "@Sex";

            par[3].dbType = SqlDbType.VarChar;
            par[3].objValue = model.HomePage;
            par[3].parName = "@HomePage";


            par[4].dbType = SqlDbType.VarChar;
            par[4].objValue = model.QQ;
            par[4].parName = "@QQ";

            par[5].dbType = SqlDbType.VarChar;
            par[5].objValue = model.Msn;
            par[5].parName = "@MSN";

            par[6].dbType = SqlDbType.VarChar;
            par[6].objValue = model.Birthday;
            par[6].parName = "@birthday";

            par[7].dbType = SqlDbType.VarChar;
            par[7].objValue = model.Quesion;
            par[7].parName = "@Quesion";

            par[8].dbType = SqlDbType.VarChar;
            par[8].objValue = model.Answer;
            par[8].parName = "@Answer";

            par[9].dbType = SqlDbType.VarChar;
            par[9].objValue = model.UserMobile;
            par[9].parName = "@UserMobile";

            #endregion


            db Con = new db();
            try
            {
                result = Con.ExcSql("Update  [Users] Set Sex=@Sex,Quesion=@Quesion,Answer=@Answer,UserEmail=@UserEmail,birthday=@birthday,UserMobile=@UserMobile,HomePage=@HomePage,QQ=@QQ,MSN=@MSN  where Userid=@UserId", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);

            }
            finally
            {
                Con.close();

            }


            return result;
        }

        /// <summary>
        /// 更新用户(后台修改)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AdminUpdate(Users_Model model)
        {
            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[19];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue = model.UserId;
            par[0].parName = "@UserId";

            par[1].dbType = SqlDbType.VarChar;
            par[1].objValue = model.UserEmail;
            par[1].parName = "@UserEmail";

            par[2].dbType = SqlDbType.VarChar;
            par[2].objValue = model.Sex;
            par[2].parName = "@Sex";

            par[3].dbType = SqlDbType.VarChar;
            par[3].objValue = model.HomePage;
            par[3].parName = "@HomePage";


            par[4].dbType = SqlDbType.VarChar;
            par[4].objValue = model.QQ;
            par[4].parName = "@QQ";

            par[5].dbType = SqlDbType.VarChar;
            par[5].objValue = model.Msn;
            par[5].parName = "@MSN";

            par[6].dbType = SqlDbType.VarChar;
            par[6].objValue = model.Birthday;
            par[6].parName = "@birthday";

            par[7].dbType = SqlDbType.VarChar;
            par[7].objValue = model.Quesion;
            par[7].parName = "@Quesion";

            par[8].dbType = SqlDbType.VarChar;
            par[8].objValue = model.Answer;
            par[8].parName = "@Answer";

            par[9].dbType = SqlDbType.VarChar;
            par[9].objValue = model.UserMobile;
            par[9].parName = "@UserMobile";

            par[10].dbType = SqlDbType.VarChar;
            par[10].objValue = model.Pwd;
            par[10].parName = "@Pwd";

            par[11].dbType = SqlDbType.Int;
            par[11].objValue = model.LockUser;
            par[11].parName = "@LockUser";

            par[12].dbType = SqlDbType.VarChar;
            par[12].objValue = model.AddTime;
            par[12].parName = "@AddTime";

            par[13].dbType = SqlDbType.Int;
            par[13].objValue = model.Article;
            par[13].parName = "@Article";

            par[14].dbType = SqlDbType.Int;
            par[14].objValue = model.City;
            par[14].parName = "@City";

            par[15].dbType = SqlDbType.Int;
            par[15].objValue = model.Province;
            par[15].parName = "@Province";

            par[16].dbType = SqlDbType.Text;
            par[16].objValue = model.Readme;
            par[16].parName = "@Readme";

            par[17].dbType = SqlDbType.Int;
            par[17].objValue = model.UserDel;
            par[17].parName = "@UserDel";

            par[18].dbType = SqlDbType.VarChar;
            par[18].objValue = model.UserPhoto;
            par[18].parName = "@UserPhoto";

            #endregion


            db Con = new db();
            try
            {
                string cmdText = "Update  [Users] Set Sex=@Sex,Quesion=@Quesion,Answer=@Answer,UserEmail=@UserEmail,birthday=@birthday,UserMobile=@UserMobile,HomePage=@HomePage,QQ=@QQ,MSN=@MSN,Pwd=@Pwd,LockUser=@LockUser,AddTime=@AddTime,Article=@Article,City=@City,Province=@Province,Readme=@Readme,UserDel=@UserDel,UserPhoto=@UserPhoto where Userid=@UserId";
                result = Con.ExcSql(cmdText, par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);

            }
            finally
            {
                Con.close();

            }


            return result;
        }


        public int Add(Users_Model model)
        {
            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[20];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue = 0;
            par[0].parName = "@UserId";

            par[1].dbType = SqlDbType.VarChar;
            par[1].objValue = model.UserName;
            par[1].parName = "@UserName";

            par[2].dbType = SqlDbType.VarChar;
            par[2].objValue = model.UserEmail;
            par[2].parName = "@UserEmail";


            par[3].dbType = SqlDbType.Int;
            par[3].objValue = model.Article;
            par[3].parName = "@Article";


            par[4].dbType = SqlDbType.VarChar;
            par[4].objValue = model.Pwd;
            par[4].parName = "@Pwd";

            par[5].dbType = SqlDbType.VarChar;
            par[5].objValue = model.Sex;
            par[5].parName = "@Sex";

            par[6].dbType = SqlDbType.VarChar;
            par[6].objValue = model.HomePage;
            par[6].parName = "@HomePage";


            par[7].dbType = SqlDbType.VarChar;
            par[7].objValue = model.QQ;
            par[7].parName = "@QQ";

            par[8].dbType = SqlDbType.VarChar;
            par[8].objValue = model.Msn;
            par[8].parName = "@MSN";

            par[9].dbType = SqlDbType.VarChar;
            par[9].objValue = StringHelper.Ip();
            par[9].parName = "@LastIP";


            par[10].dbType = SqlDbType.VarChar;
            par[10].objValue = model.Birthday;
            par[10].parName = "@birthday";

            par[11].dbType = SqlDbType.VarChar;
            par[11].objValue = model.Quesion;
            par[11].parName = "@Quesion";

            par[12].dbType = SqlDbType.VarChar;
            par[12].objValue = model.Answer;
            par[12].parName = "@Answer";

            par[13].dbType = SqlDbType.VarChar;
            par[13].objValue = model.UserMobile;
            par[13].parName = "@UserMobile";

            par[14].dbType = SqlDbType.Decimal;
            par[14].objValue = model.Point;
            par[14].parName = "@userpoint";

            par[15].dbType = SqlDbType.Int;
            par[15].objValue = model.Province;
            par[15].parName = "@province";

            par[16].dbType = SqlDbType.Decimal;
            par[16].objValue = model.Integral;
            par[16].parName = "@integral";

            par[17].dbType = SqlDbType.Decimal;
            par[17].objValue = model.LockUser;
            par[17].parName = "@lockuser";

            par[18].dbType = SqlDbType.Text;
            par[18].objValue = model.Readme;
            par[18].parName = "@Readme";


            par[19].dbType = SqlDbType.Int;
            par[19].objValue = model.City;
            par[19].parName = "@City";


            #endregion

            db Cn = new db();
            try
            {
                result = Cn.ExcSql("Insert into Users(UserName,UserEmail,Article,Pwd,Sex,HomePage,QQ,MSN,LastIP,lockuser,birthday,UserMobile,Quesion,Answer,userpoint,province,City,integral,Readme)values(@UserName,@UserEmail,@Article,@Pwd,@Sex,@HomePage,@QQ,@MSN,@LastIP,@lockuser,@birthday,@UserMobile,@Quesion,@Answer,@userpoint,@province,@City,@integral,@Readme)", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);
            }
            finally
            {
                Cn.close();
            }
            return result;
        }

        public Users_Model GetModel(System.Data.DataRow dr)
        {
            Users_Model model = new Users_Model();
            try
            {
                if (dr["Userid"] != null) { model.UserId = StringHelper.isNumber_int(dr["Userid"].ToString()); }
                if (dr["UserName"] != null) { model.UserName = dr["UserName"].ToString(); }
                if (dr["UserEmail"] != null) { model.UserEmail = dr["UserEmail"].ToString(); }
                if (dr["Article"] != null) { model.Article = StringHelper.isNumber_int(dr["Article"].ToString()); }
                if (dr["Pwd"] != null) { model.Pwd = dr["Pwd"].ToString(); }
                if (dr["Sex"] != null) { model.Sex = dr["Sex"].ToString(); }
                if (dr["HomePage"] != null) { model.HomePage = dr["HomePage"].ToString(); }
                if (dr["Addtime"] != null) { model.AddTime = dr["Addtime"].ToString(); }
                if (dr["logins"] != null) { model.Logins = StringHelper.isNumber_int(dr["Logins"].ToString()); }
                if (dr["QQ"] != null) { model.QQ = dr["QQ"].ToString(); }
                if (dr["MSN"] != null) { model.Msn = dr["MSN"].ToString(); }
                if (dr["lastlogin"] != null) { model.LastLogin = Convert.ToDateTime(dr["lastlogin"].ToString()); }
                if (dr["loginOutTime"] != null) { model.loginOutTime = Convert.ToDateTime(dr["loginOutTime"].ToString()); }
                if (dr["OnlineTime"] != null) { model.OnlineTime = StringHelper.isNumber_decimal(dr["OnlineTime"].ToString()); }
                if (dr["LastIP"] != null) { model.LastIP = dr["LastIP"].ToString(); }
                if (dr["lockuser"] != null) { model.LockUser = StringHelper.isNumber_int(dr["LockUser"].ToString()); }
                if (dr["birthday"] != null) { model.Birthday = dr["Birthday"].ToString(); }
                if (dr["Quesion"] != null) { model.Quesion = dr["Quesion"].ToString(); }
                if (dr["Answer"] != null) { model.Answer = dr["Answer"].ToString(); }
                if (dr["UserPhoto"] != null) { model.UserPhoto = dr["UserPhoto"].ToString(); }
                if (dr["UserDel"] != null) { model.UserDel = StringHelper.isNumber_int(dr["UserDel"].ToString()); }
                if (dr["UserMobile"] != null) { model.UserMobile = dr["UserMobile"].ToString(); }
                if (dr["userpoint"] != null) { model.Point = StringHelper.isNumber_decimal(dr["userpoint"].ToString()); }
                if (dr["province"] != null) { model.Province = StringHelper.isNumber_int(dr["province"].ToString()); }
                if (dr["City"] != null) { model.City = StringHelper.isNumber_int(dr["City"].ToString()); }
                if (dr["integral"] != null) { model.Integral = StringHelper.isNumber_decimal(dr["integral"].ToString()); }
                if (dr["Readme"] != null) { model.Readme = dr["Readme"].ToString(); }
                if (dr["IsOnline"] != null) { model.IsOnline = StringHelper.isNumber_int(dr["IsOnline"].ToString()); }
            }
            catch (Exception ex)
            {
                new CustomException(ex);
            }
            return model;
        }

        public int Del(int id)
        {
            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[1];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue =id;
            par[0].parName = "@UserId";

            #endregion


            db Con = new db();
            try
            {
                result = Con.ExcSql("delete from [Users]  where Userid=@UserId", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);

            }
            finally
            {
                Con.close();

            }


            return result;
        }

        public int UserLock(int UserId)
        {
            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[2];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue = UserId;
            par[0].parName = "@UserId";

            par[1].dbType = SqlDbType.VarChar;
            par[1].objValue = 1;
            par[1].parName = "@lockuser";

            #endregion


            db Con = new db();
            try
            {
                result = Con.ExcSql("Update  [Users] Set lockuser=@lockuser where Userid=@UserId", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);

            }
            finally
            {
                Con.close();

            }


            return result;
        }

        public int UserUnLock(int UserId)
        {
            int result = -1;
            #region 参数

            db.ParameterArry[] par = new db.ParameterArry[2];
            par[0].dbType = SqlDbType.Int;
            par[0].objValue = UserId;
            par[0].parName = "@UserId";

            par[1].dbType = SqlDbType.VarChar;
            par[1].objValue = 0;
            par[1].parName = "@lockuser";

            #endregion


            db Con = new db();
            try
            {
                result = Con.ExcSql("Update  [Users] Set lockuser=@lockuser where Userid=@UserId", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);

            }
            finally
            {
                Con.close();

            }


            return result;
        }

        /// <summary>
        /// 检查用户名是否存在
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int IsObjUserName(string UserName)
        {
            int result = 0;
            db.ParameterArry[] par = new db.ParameterArry[1];
            par[0].dbType = SqlDbType.VarChar;
            par[0].objValue = UserName;
            par[0].parName = "@UserName";
            db Conn = new db();
            try
            {
                result = (int)Conn.GetOneFile("Select Count(Userid) From Users Where UserName=@UserName", par);
            }
            catch (Exception ex)
            {
                new CustomException(ex);
            }
            finally
            {
                Conn.close();
            }
            return result;

        }
        public int UserLogin(Users_Model model)
        {

            int Result = 0;
            db ConnUser = new db();
            try
            {
                db.ParameterArry[] Par = new db.ParameterArry[2];
                Par[0].dbType = SqlDbType.VarChar;
                Par[0].objValue = model.UserName;
                Par[0].parName = "@Un";


                Par[1].dbType = SqlDbType.VarChar;
                Par[1].objValue = model.Pwd;
                Par[1].parName = "@Pwd";
                SqlDataReader Sdr = ConnUser.GetSqlDataReader("Select Top 1 * from [Users] Where UserName=@Un and Pwd=@Pwd", Par);
                while (Sdr.Read())
                {
                    if (Sdr["lockuser"].ToString() == "1")
                    {
                        Result = -2;
                    }
                    else
                    {
                        Result = 1;//成功
                        model.UserId = StringHelper.isNumber_int(Sdr["UserId"].ToString());
                    }
                }
            }
            catch (Exception Ex)
            {
                Result = -1;
                new CustomException(Ex);
            }
            finally
            {
                if (Result == 1)
                {
                    UpdateLastLoginTime("  and UserId=" + model.UserId + "");
                }

                ConnUser.close();
            }
            return Result;

        }

        /// <summary>
        /// 返回DATATABLE
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string filter, SqlParameter[] sp)
        {
            db Conndt = new db();

            DataTable dt = new DataTable();
            try
            {
                string sqlstr = "Select * from Users Where Userid<>0 " + filter;
                dt = Conndt.GetSql_DataTable(sqlstr, sp);
            }
            catch (Exception ex)
            {
                new CustomException(ex);
            }
            finally
            {
                Conndt.close();
            }
            return dt;
        }

        /// <summary>
        /// 更新最后登录成功的时间,与IP
        /// </summary>
        /// <param name="Filter">条件，请直接以" and "开头</param>
        public void UpdateLastLoginTime(string Filter)
        {

            db Conn = new db();
            Conn.Execute_Noget("Update [Users] Set  loginOutTime=RealTime,LastIP='" + StringHelper.Ip() + "',IsOnline=1,RealTime='" + DateTime.Now + "', LastLogin='" + DateTime.Now + "' Where Lockuser=0  " + Filter);
            Conn.close();
        }
        /// <summary>
        /// 更新用户最后退出时间
        /// </summary>
        /// <param name="Filter"></param>
        public void UpdateLastOutTime(string Filter)
        {
            string Sqlstr = "  Update [Users] Set IsOnline=0,loginOutTime='" + DateTime.Now + "',OnlineTime=OnlineTime+datediff(n,lastlogin,getdate()) Where Lockuser=0   " + Filter;
            db Conn = new db();
            Conn.Execute_Noget(Sqlstr);
            Conn.close();
        }
        /// <summary>
        /// 检查用户是否在线
        /// </summary>
        /// <param name="Filter">Filter=""时，则更新表中的所有用户!where条件,请直接以" and "开头</param>
        /// <param name="checkTime_Int"></param>
        public void CheckUserOnline(string Filter, int checkTime_Int)
        {
            DateTime Currtime = DateTime.Now;
            string sqlstr = "  Update Users set  IsOnline=(case when datediff(n,RealTime,'" + Currtime + "')>" + checkTime_Int + " then 0  else 1 end )";
            if (Filter != "")
            {
                sqlstr += ", OnlineTime=OnlineTime+(case when abs(datediff(n,RealTime,getdate()))>" + checkTime_Int + " then 0  else   (datediff(n,RealTime,'" + Currtime + "'))end ),";
                sqlstr += "  RealTime='" + Currtime + "'";
            }
            sqlstr += " where  Lockuser=0 " + Filter;
            db Conn = new db();
            Conn.Execute_Noget(sqlstr);
            Conn.close();
        }

        /// <summary>
        /// 扣除用户点数
        /// </summary>
        /// <param name="Point">扣除的点数</param>
        /// <param name="UserId">用户编号</param>
        public void DrePoint(int Point,int UserId)
        {
            DateTime Currtime = DateTime.Now;
            string sqlstr = "Update Users set userpoint=userpoint-(@userpoint) where UserId=@UserId";
            db.ParameterArry[] paras=new db.ParameterArry[2];

            paras[0].dbType = SqlDbType.Decimal;
            paras[0].parName = "@userpoint";
            paras[0].objValue =Math.Abs(Point);

            paras[1].dbType = SqlDbType.BigInt;
            paras[1].parName = "@UserId";
            paras[1].objValue = UserId;


            db Conn = new db();
            Conn.ExcSql(sqlstr, paras);
            Conn.close();
        }

        #endregion
    }
}
